#!/usr/bin/python3
# -*- coding:utf-8 -*-
""""
@Author: 李吉凯
@Email: lijk34925@hundsun.com
@Time: 2021/8/24 8:45
@File: DataOprationOfExcel.py
"""
import os
import pandas as pd
import traceback
from BladeFerm.Lib.Common import *
from BladeFerm.utils.excel_operate import ExcelOfWin32Com, ExcelOfPandas
from BladeFerm.utils.basic_opration import is_dir_exist

def modifyExcel(filepath, sheet_name, pos, dataList):
    '''
    :param filepath: 全路径 //'E:\\Web_AutoTest\\FERM_FSK\\全面风险管理\\web应用\\test_BladeFrame\\data\\moudle_raw.xls'
    :param sheet_name: 表格的sheet页名称 //'外部损失件导入模板_1'
    :param pos: 数据写入的单元格其实位置 //'C9'
    :param dataList: 写入数据，可以是多行，也可以是单行 
        多行输入：//[['风险管理部', '2021-8-23', '风险管理部,员工违规使用电脑', 5000, 40000, '资产管理'],
            ['风险管理部', '2021-8-21', '风险管理部,员工违规使用网络', 200, 2000, '代理销售']]
        单行输入：// ['风险管理部', '2021-8-23', '风险管理部,员工违规使用电脑', 5000, 40000, '资产管理']
    :return: 写入的结果，True / False
    '''
    flag = False
    logger.info(f'Excel路径：{filepath}')
    excl_op = ExcelOfWin32Com()
    try:
        logger.info(f'写入数据:{dataList}')
        excl_op.read_excel(filepath=filepath, sheet_name=sheet_name)
        excl_op.filedata(pos, dataList)
        flag = True
    except Exception as e:
        logger.error(f'写入出现异常：{e}')
    finally:
        excl_op.close_excel()
    return flag


def load_Test_datas(filepath, sheet_names=['web元素','测试数据','期望结果']):
    '''
    Function: 为pytest用例参数化准备测试数据，读取excel表格中的数据，将其组合成测试数据
    :param filepath: 文件路径
    :param sheet_names: sheet名称，顺序不可变，sheet名称与excel保持一致。缺省值是 ['web元素','测试数据','期望结果']
    :return: 
    '''
    newList = []
    logger.info('Starting collect testUnit data.')
    ep = ExcelOfPandas()
    # 读Excel的3个sheet页信息
    sheets = ep.readExcel(filepath, sheet_names)
    html_df = sheets[sheet_names[0]]
    html_df = ep.sub_None(html_df)
    data_df = sheets[sheet_names[1]]
    data_df = ep.sub_None(data_df)
    expc_df = sheets[sheet_names[2]]
    expc_df = ep.sub_None(expc_df)
    # web元素转换dict
    html_lst = ep.get_df_colnames(html_df)
    html_dic = ep.zip_df_dic(html_df, html_lst[0], html_lst[1])
    logger.info(f'html_dic: {html_dic}')
    # 测试数据
    data_lst = ep.get_df_colnames(data_df)
    # 期望数据
    expc_lst = ep.get_df_colnames(expc_df)
    for i in range(2, len(data_lst)):
        data_dic = ep.zip_df_dic(data_df, data_lst[0], data_lst[i])
        logger.info(f'data_dic: {data_dic}')
        if i < len(expc_lst):
            exp_dic = ep.zip_df_dic(expc_df, expc_lst[0], expc_lst[i])
        else:
            exp_dic = {}
        logger.info(f'exp_dic: {exp_dic}')
        temp_tup = (html_dic, data_dic, exp_dic)
        newList.append(temp_tup)
    return newList

def read_Excel_data(filepath, sheet_name, key_index=0, col_index=[1]):
    '''
    Function: 加载excel表中的测试数据，返回列表，方便参数化
    :param filepath: 文件路径
    :param sheet_names: 读取excel的数据
    :param col_index: 测试数据开始的列
    :return newList: 返回的数据，结构是： [(web元素，测试数据1，期望1),(web元素，测试数据2，期望2),...]
    '''
    newList = []
    excel_op = ExcelOfPandas()
    sheet1_data = excel_op.readExcel(filepath, sheet_name)
    col_list_01 = excel_op.get_names()
    for col in col_index:
        temp_dic = excel_op.zip_dic(col_list_01[key_index], col_list_01[col])
        newList.append(temp_dic)
    return newList

def write_excel(data_dic, filepath, sheet='sheet1', data_type=0):
    '''
    将字典数据写入excel，方便将py脚本中写的测试数据填到excel中，直接拷贝到blase平台
    eg：
        write_excel(input_elements, 'data.xlsx', sheet='web元素')
        write_excel(input_data, 'data2.xlsx', sheet='测试数据', data_type=1)
        write_excel(ui_except, 'data3.xlsx', sheet='期望结果', data_type=1)
    :param data_dic: 写入的字典数据
    :param filepath: excel文件全路径
    :param sheet: sheet页名称
    :param data_type: 0，写如2行，Key value之间没有空行；1，写入3行，key value之间保持空行
    :return:
    '''
    ep = ExcelOfPandas()
    write_list = []
    key_list = data_dic.keys()
    value_list = data_dic.values()
    write_list.append(key_list)
    if data_type != 0:
        write_list.append([])
    write_list.append(value_list)
    data_df = ep.data_to_DF(write_list)
    ep.write_excel(filepath, data_df, sheet)
    return True

def row_to_col(readFilePath, writeFilePath, readSheets=['web元素', '测试数据', '期望结果'], writeSheets=['测试数据', 'web元素']):
    '''
    读取excel数据，将其转化为blade平台上的输入数据；可以直接导入数据即可
    :param readFilePath:
    :param writeFilePath:
    :return:
    '''
    try:
        ep = ExcelOfPandas()
        ecls = ep.readExcel(readFilePath, sheet_name=readSheets)
        # 测试数据处理逻辑
        data_df_raw = ecls[readSheets[1]]
        data_num = len(ep.get_df_colnames(data_df_raw)) - 2
        logger.info(f'测试数据有 {data_num} 组')
        data_df = data_df_raw.transpose()
        first_lst = ['序号', '参数说明']
        secd_lst = ['期望', None]
        last_lst = ['Elements(Object)', None]
        exp_df = ecls[readSheets[2]]
        exp_names = ep.get_df_colnames(exp_df)
        exp_num = len(exp_names) - 2
        logger.info(f'期望结果有 {exp_num} 组')
        # 将excel的期望数据填入，要求期望数据和测试数据必须相等时才填写，否则就默认给空值
        if exp_num == data_num:
            key_lst = ep.readColOfDF(exp_df, exp_names[0])
            for i in range(2, exp_num+2):
                value_lst = ep.readColOfDF(exp_df, exp_names[i])
                t_dic = zip(key_lst, value_lst)
                t_lst = [str(k) + '=' + str(v) for k, v in t_dic]
                first_lst.append(None)
                secd_lst.append(';'.join(t_lst))
                last_lst.append(f'{writeSheets[1]}|1')
        else:
            first_lst += [None] * data_num
            secd_lst += [None] * data_num
            last_lst += [f'{writeSheets[1]}|1'] * data_num
            logger.warn(f'期望数据和测试数据的组数不相同，自动填充空')
        data_cols = len(ep.get_df_colnames(data_df))
        data_df.insert(0, 'first', first_lst)
        data_df.insert(1, 'second', secd_lst)  # ['期望', None, '校验结果=True']
        data_df.insert(data_cols+2, 'last', last_lst)        #['Elements(Object)', None, 'Sheet1|1']
        # web元素处理逻辑
        web_df = ecls[readSheets[0]].transpose()
        web_df.insert(0, 'first', ['GroupID', '1'])
        # 写入excel
        writer = pd.ExcelWriter(writeFilePath)
        data_df.to_excel(writer, writeSheets[0], index=False, header=False)
        web_df.to_excel(writer, writeSheets[1], index=False, header=False)
        writer.save()
    except Exception as e:
        logger.debug(traceback.print_exc())
        logger.error(e)
        logger.warn(f'检查 {readFilePath} 表格数是否正确。')
    return

def excel_to_change(dir_Path, new_name='Blade_data',
                    readSheets=['web元素', '测试数据', '期望结果'],
                    writeSheets=['测试数据', 'web元素']):
    '''
    读取目录下所有的Excel数据，将本地调试的数据转置后，方便直接导入到测试平台
    :param dir_Path: 测试数据的目录
    :param new_name: 新建子文件名称
    :param readSheets: sheet页的名称，与本地调试使用的sheet名称保持一致
    :return:
    '''
    new_path = os.path.join(dir_Path, new_name)
    is_dir_exist(new_path)
    file_lst = os.listdir(dir_Path)
    for file in file_lst:
        name, ext = os.path.splitext(file)
        if ext == '.xls' or ext == '.xlsx':
            read_path = os.path.join(dir_Path, file)
            write_name = 'Blade_' + file
            write_path = os.path.join(new_path, write_name)
            logger.info(f'read_path: {read_path}')
            logger.info(f'write_path: {write_path}')
            row_to_col(read_path, write_path, readSheets, writeSheets)
        else:
            logger.warn(f'{file} no support!')
    return new_path